package com.ui.panel;

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.FocusEvent;
import java.awt.event.FocusListener;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.Vector;

import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JFileChooser;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.border.TitledBorder;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.filechooser.FileSystemView;
import javax.swing.table.DefaultTableModel;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;

import com.ui.Application;
import com.ui.export.utils.ExpSql;
import com.ui.table.RowHeadRender;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;

public class ExportSqlPanel extends JPanel implements ActionListener {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Connection conn;
	private JTextField menuitemcodeText, appcodeText, filePathText; // 表明，实体ID,文件存储路径
	private JButton browseBtn, okBtn, cancelBtn, addLineBtn, delLineBtn; // 浏览按钮，生成脚本，推出按钮
	private JCheckBox isNeedChildrenBox;
	private Application parentContainer; // 上级组件
	private DefaultTableModel tableModel = new DefaultTableModel(null, new String[] { "", "表名", "描述", "where" });;
	private JTable table = new JTable(tableModel);
	Set<String> moduleIds = new TreeSet<String>();

	public ExportSqlPanel(Application parentContainer, Connection conn) {
		this.conn = conn;
		this.parentContainer = parentContainer;
		initUI();
	}

	/**
	 * 初始化UI
	 */
	public void initUI() {
		this.setLayout(new BorderLayout());
		this.add(getUpPanel(), BorderLayout.NORTH);
		this.add(getLogPanel());
		this.add(getBtnPanel(), BorderLayout.SOUTH);
		initTable();
	}

	public void initTable() {
		table.getColumnModel().getColumn(0).setCellRenderer(new RowHeadRender(table));
		table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		table.getColumnModel().getColumn(0).setPreferredWidth((int) (550 * 0.05));
		table.getColumnModel().getColumn(1).setPreferredWidth((int) (550 * 0.25));
		table.getColumnModel().getColumn(2).setPreferredWidth((int) (550 * 0.25));
		table.getColumnModel().getColumn(3).setPreferredWidth((int) (1500 * 0.7));
		tableModel.addTableModelListener(new TableModelListener() {

			@Override
			public void tableChanged(TableModelEvent e) {
				if (e.getType() == TableModelEvent.INSERT || e.getType() == TableModelEvent.DELETE) {
					for (int i = 0; i < tableModel.getRowCount(); i++) {
						tableModel.setValueAt((i + 1), i, 0);
					}

				}

			}
		});
	}

	/**
	 * 日志面板
	 * 
	 * @return
	 */
	private JPanel getLogPanel() {
		JPanel upPanel = new JPanel();
		TitledBorder titledBorder = BorderFactory.createTitledBorder("日志信息");
		titledBorder.setTitleJustification(TitledBorder.LEFT);
		upPanel.setBorder(titledBorder);
		upPanel.add(getLogArea());
		return upPanel;
	}

	/**
	 * 按钮面板
	 * 
	 * @return
	 */
	private JPanel getBtnPanel() {
		JPanel btnPanel = new JPanel();
		btnPanel.add(getOkBtn());
		btnPanel.add(getCancelBtn());
		return btnPanel;
	}

	/**
	 * 主要信息面板
	 * 
	 * @return
	 */
	private JPanel getUpPanel() {
		JPanel upPanel = new JPanel();
		TitledBorder titledBorder = BorderFactory.createTitledBorder("实体信息");
		titledBorder.setTitleJustification(TitledBorder.LEFT);
		upPanel.setLayout(new BorderLayout());
		upPanel.setBorder(titledBorder);
		upPanel.add(getInfoPanel(), BorderLayout.NORTH);
		upPanel.add(getFilePanel(), BorderLayout.SOUTH);
		upPanel.add(getMainPanel());
		return upPanel;
	}

	/**
	 * 实体信息面板
	 * 
	 * @return
	 */
	private JPanel getInfoPanel() {
		JPanel infoPanel = new JPanel();
		infoPanel.add(new JLabel("应用编码:"));
		infoPanel.add(getAppcodeText());
		infoPanel.add(new JLabel("菜单编码:"));
		infoPanel.add(getMenuitemcodeText());
		infoPanel.add(new JLabel("是否包含下级:"));
		infoPanel.add(getIsNeedChildrenBox());
		return infoPanel;
	}

	/**
	 * 表名文本框
	 * 
	 * @return
	 */
	private JCheckBox getIsNeedChildrenBox() {
		if (isNeedChildrenBox == null) {
			isNeedChildrenBox = new JCheckBox();
			isNeedChildrenBox.setSelected(true);
			isNeedChildrenBox.addActionListener(new ActionListener() {

				@Override
				public void actionPerformed(ActionEvent e) {
					try {
						String appcode = getAppcodeText().getText();
						if (StrUtil.isNotEmpty(appcode)) {

							generateExportData(appcode, getIsNeedChildrenBox().isSelected());

						}

						String menucode = getMenuitemcodeText().getText();
						if (StrUtil.isNotEmpty(menucode)) {
							getMenucodes(menucode, getIsNeedChildrenBox().isSelected());
						} else {
//						JOptionPane.showMessageDialog(null, "应用注册编码不能为空", "错误", 0);
						}
					} catch (SQLException e1) {
						logger(e1.toString());
					}
				}
			});
		}
		return isNeedChildrenBox;
	}

	/**
	 * 实体ID文本框
	 * 
	 * @return
	 */
	private JTextField getAppcodeText() {
		if (appcodeText == null) {
			appcodeText = new JTextField(35);
			appcodeText.setToolTipText("请输入应用注册编码，多个用逗号隔开");
			appcodeText.addKeyListener(new KeyListener() {
				
				@Override
				public void keyTyped(KeyEvent e) {
					
				}
				
				@Override
				public void keyReleased(KeyEvent e) {
					
				}
				
				@Override
				public void keyPressed(KeyEvent e) {
					if (e.getKeyChar() == KeyEvent.VK_ENTER) {
						getMenuitemcodeText().requestFocus();
					}
					
				}
			});
			appcodeText.addFocusListener(new FocusListener() {

				@Override
				public void focusLost(FocusEvent e) {
					try {
						if (conn == null || conn.isClosed()) {
							connect();
						}
						String appcode = getAppcodeText().getText();
						if (StrUtil.isNotEmpty(appcode)) {
							generateExportData(appcode, getIsNeedChildrenBox().isSelected());
							getMenucodes(getMenuitemcodeText().getText(),  getIsNeedChildrenBox().isSelected());
						} else {
//							JOptionPane.showMessageDialog(null, "应用注册编码不能为空", "错误", 0);
						}

					} catch (Exception e1) {
						JOptionPane.showMessageDialog(null, e1, "错误", 0);
					}

				}

				@Override
				public void focusGained(FocusEvent e) {
//					try {
//						if (conn != null && !conn.isClosed()) {
//
//						} else {
//							logger("连接数据库失败，不根据数据库处理...");
//							JOptionPane.showMessageDialog(null, "数据库未连接", "错误", 0);
//							parentContainer.getHostText().requestFocus();
//						}
//					} catch (Exception e2) {
//						logger(e.toString());
//					}
				}
			});
		}
		return appcodeText;
	}

	/**
	 * 实体ID文本框
	 * 
	 * @return
	 */
	private JTextField getMenuitemcodeText() {
		if (menuitemcodeText == null) {
			menuitemcodeText = new JTextField(35);
			menuitemcodeText.setToolTipText("请输入菜单注册编码，多个用逗号隔开");
			menuitemcodeText.addKeyListener(new KeyListener() {
				
				@Override
				public void keyTyped(KeyEvent e) {
					
				}
				
				@Override
				public void keyReleased(KeyEvent e) {
					
				}
				
				@Override
				public void keyPressed(KeyEvent e) {
					if (e.getKeyChar() == KeyEvent.VK_ENTER) {
						parentContainer.getLogText2().requestFocus();
					}
					
				}
			});
			menuitemcodeText.addFocusListener(new FocusListener() {

				@Override
				public void focusLost(FocusEvent e) {
					try {
						if (conn == null || conn.isClosed()) {
							connect();
						}
						String appcode = getMenuitemcodeText().getText();
						if (StrUtil.isNotEmpty(appcode)) {
							getMenucodes(appcode, getIsNeedChildrenBox().isSelected());
						} else {
//							JOptionPane.showMessageDialog(null, "应用注册编码不能为空", "错误", 0);
						}

					} catch (Exception e1) {
						JOptionPane.showMessageDialog(null, e1, "错误", 0);
					}

				}

				@Override
				public void focusGained(FocusEvent e) {
				}
			});
		}
		return menuitemcodeText;
	}

	/**
	 * 路径文本框
	 * 
	 * @return
	 */
	private JTextField getFilePathText() {
		if (filePathText == null) {
			filePathText = new JTextField(43);
		}
		return filePathText;
	}

	/**
	 * 选择路径按钮
	 * 
	 * @return
	 */
	private JButton getBrowseBtn() {
		if (browseBtn == null) {
			browseBtn = new JButton("浏览");
			browseBtn.addActionListener(this);
		}
		return browseBtn;
	}

	/**
	 * 生成脚本按钮
	 * 
	 * @return
	 */
	private JButton getOkBtn() {
		if (okBtn == null) {
			okBtn = new JButton("导出脚本");
			okBtn.addActionListener(this);
		}
		return okBtn;
	}

	/**
	 * 推出按钮
	 * 
	 * @return
	 */
	private JButton getCancelBtn() {
		if (cancelBtn == null) {
			cancelBtn = new JButton("退出");
			cancelBtn.addActionListener(this);
		}
		return cancelBtn;
	}

	/**
	 * 日志域
	 * 
	 * @return
	 */
	private JScrollPane getLogArea() {
		return new JScrollPane(this.parentContainer.getLogText2());
	}

	/**
	 * 获取导出信息
	 * 
	 * @return
	 */
	private JPanel getMainPanel() {
		JPanel upPanel = new JPanel();
		TitledBorder titledBorder = BorderFactory.createTitledBorder("导出表信息");
		titledBorder.setTitleJustification(TitledBorder.LEFT);
		upPanel.setBorder(titledBorder);
		upPanel.setLayout(new BorderLayout());
		upPanel.add(getLineBtnPanel(), BorderLayout.EAST);
		upPanel.add(getTablePanel());
		return upPanel;
	}

	private JPanel getLineBtnPanel() {
		JPanel defPanel = new JPanel();
		defPanel.add(getAddLineBtn());
		defPanel.add(getDelLineBtn());
		defPanel.setLayout(new GridLayout(4, 1));
		return defPanel;
	}

	public JButton getAddLineBtn() {
		if (addLineBtn == null) {
			addLineBtn = new JButton("增行");
			addLineBtn.addActionListener(this);
		}
		return addLineBtn;
	}

	public JButton getDelLineBtn() {
		if (delLineBtn == null) {
			delLineBtn = new JButton("删行");
			delLineBtn.addActionListener(this);
		}
		return delLineBtn;
	}

	/**
	 * 表格面板
	 * 
	 * @return
	 */
	private JScrollPane getTablePanel() {
		JScrollPane panel = new JScrollPane(table);
		panel.setPreferredSize(new Dimension(400, 150));
		return panel;
	}

	/**
	 * 存储文件区域面板
	 * 
	 * @return
	 */
	private JPanel getFilePanel() {
		JPanel filePanel = new JPanel();
		filePanel.add(getFilePathText());
		filePanel.add(getBrowseBtn());
		return filePanel;
	}

	/**
	 * 记录日志
	 * 
	 * @param message
	 */
	private void logger(String message) {
		parentContainer.getLogText2().append(message);
		parentContainer.getLogText2().append(System.lineSeparator());
		parentContainer.getLogText2().setCaretPosition(parentContainer.getLogText2().getText().length());
	}

	private void connect() {
		try {
			logger("开始连接数据库...");
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
			String url = parentContainer.getHostText().getText();
			String uname = parentContainer.getUserText().getText();
			String pwd = parentContainer.getPwdText().getText();
			if (StrUtil.isNotEmpty(url) && StrUtil.isNotEmpty(uname) && StrUtil.isNotEmpty(pwd)) {
				DbUtils.loadDriver("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(url, uname, pwd);
				logger("连接数据库成功...");
			} else {
				logger("数据库信息不完整不尝试连接...");
			}

		} catch (Exception e) {
			logger("连接数据库失败：" + e);
			JOptionPane.showMessageDialog(addLineBtn, "连接数据库失败：" + e, "错误", 0);
		}
	}

	/**
	 * 按钮事件
	 */
	@SuppressWarnings("unchecked")
	@Override
	public void actionPerformed(ActionEvent e) {
		if (e.getSource() == this.browseBtn) {
			JFileChooser fileChooser = new JFileChooser();
			FileSystemView fileSystemView = FileSystemView.getFileSystemView();
			fileChooser.setCurrentDirectory(fileSystemView.getHomeDirectory());
			fileChooser.setDialogTitle("保存路径");
			fileChooser.setApproveButtonText("选择文件夹");
			fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
			int rs = fileChooser.showOpenDialog(this);
			if (rs == JFileChooser.APPROVE_OPTION) {
				this.filePathText.setText(fileChooser.getSelectedFile().getPath());
			}
		} else if (e.getSource() == this.cancelBtn) {
			this.parentContainer.dispose();
		} else if (e.getSource() == this.okBtn) {

			try {
				logger("开始导出脚本...");
				Vector<Vector<String>> datas = tableModel.getDataVector();
				if (datas != null && !datas.isEmpty()) {
					Runnable r = new Runnable() {

						@Override
						public void run() {
							try {
								String path = getFilePathText().getText();
								if (path == null || path.equals("")) {
									int rs = JOptionPane.showConfirmDialog(null, "路径为空是否保存到桌面");
									if (rs == 0)
										path = FileSystemView.getFileSystemView().getHomeDirectory().getPath();
									else
										return;
								}
								List<Object> insertSql = new ArrayList<Object>();
								List<Object> deleteSql = new ArrayList<Object>();
								for (Iterator<Vector<String>> iterator = datas.iterator(); iterator.hasNext();) {
									Vector<String> data = (Vector<String>) iterator.next();
									String table = data.get(1);
									String display = data.get(2);
									String where = data.get(3);
									if (StrUtil.isNotEmpty(table)) {

										logger("开始导出表：" + display + "(" + table + ")");
										logger("导出表条件：" + where);
										List<Object> sqls = ExpSql.getTableInsertSql(conn, table, where);
										logger("导出delete sql...");
										deleteSql.add("------------------" + display + "-------------------");
										deleteSql.add("DELETE FROM " + table + " WHERE " + where + ";");
										logger("DELETE FROM " + table + " WHERE " + where);
										logger("导出insert sql...");
										logger(CollectionUtil.join(sqls, System.lineSeparator()));
										insertSql.add("------------------" + display + "-------------------");
										insertSql.addAll(sqls);
										logger("导出表：" + table + ",结束，总计" + (sqls == null ? 0 : sqls.size()) + "条数据...");

									}
								}
								logger("导出脚本完成...");
								logger("请大概核对导出文件...");
								logger("请核对菜单注册(SM_APPMENUITEM)),参照(BD_REFINFO),导入导出翻译器( EXCEL_TRANSLATOR),导出监听( EXCEL_OUTPUTPROCESS),导入监听( EXCEL_BILLPROCESS),模块号( DAP_DAPSYSTEM)是否导出正确...");
								// 保存文件
								File dir = new File(path + "/");
								if (!dir.exists()) {
									dir.mkdir();
								}
								OutputStream out = new FileOutputStream(path + "/insert.sql");
								out.write(CollectionUtil.join(insertSql, System.lineSeparator()).getBytes());
								out.close();

								out = new FileOutputStream(path + "/delete.sql");
								out.write(CollectionUtil.join(deleteSql, System.lineSeparator()).getBytes());
								out.close();
							} catch (Exception e2) {
								logger("导出脚本出错" + e2);
							}
						}
					};
					new Thread(r).start();

				}

			} catch (Exception e2) {
				logger("导出脚本失败：" + e2);
			}

		} else if (e.getSource() == this.addLineBtn) {
			tableModel.addRow(new String[] {});
		} else if (e.getSource() == this.delLineBtn) {
			int[] selRows = table.getSelectedRows();
			if (selRows == null || selRows.length < 1) {
				JOptionPane.showMessageDialog(null, "请先选中行！");
				return;
			}

			for (int j = selRows.length - 1; j >= 0; j--) {
				tableModel.removeRow(selRows[j]);
			}
		}
	}

	void getChildAppcode(String appcode, Set<String> appcodes) throws SQLException {
		if (conn == null || conn.isClosed()) {
			connect();
		}
		if (conn != null && !conn.isClosed()) {
			String[] appcodeSplits = appcode.split(",");
			Set<String> moduleSet = new TreeSet<String>();
			appcode = "";
			for (String appcodeSplit : appcodeSplits) {
				if (appcodeSplit.length() <= 4) { // 模块号
					moduleSet.add(appcodeSplit);
				} else { // 应用编码
					appcode += appcodeSplit + ",";
				}

			}
			moduleIds.addAll(moduleSet);
			if (!StrUtil.isEmpty(appcode)) {
				appcode = appcode.substring(0, appcode.length() - 1);
			}
			QueryRunner runner = new QueryRunner();
			if (!moduleSet.isEmpty()) {
				// 根据模块号查询下级模块
				String sql = "select distinct moduleid from dap_dapsystem where parentcode in ('"
						+ CollectionUtil.join(moduleSet, ",").replace(",", "','") + "')";
				logger("查询模块下级sql:" + sql);
				List<String> childModules = runner.query(conn, sql, new ColumnListHandler<String>());
				if (!CollectionUtil.isEmpty(childModules)) {
					getChildAppcode(CollectionUtil.join(childModules, ","), appcodes);
				}
				// 根据模块号查找应用注册
				String sql1 = "select distinct code from sm_appregister where parent_id in ('"
						+ CollectionUtil.join(moduleSet, ",").replace(",", "','") + "')";
				logger("查询模块下级应用sql:" + sql);
				List<String> childAppcodes = runner.query(conn, sql1, new ColumnListHandler<String>());
				if (childAppcodes != null && !childAppcodes.isEmpty() && childAppcodes.get(0) != null) {
					appcodes.addAll(childAppcodes);
					getChildAppcode(CollectionUtil.join(childAppcodes, ","), appcodes);
				}
			}

			// 先判断是否是父模块
			String sql = "select distinct code " + "  from sm_appregister " + " where parent_id in "
					+ "       (select pk_appregister from sm_appregister where code in ('" + appcode.replace(",", "','")
					+ "')) or parent_id in ('" + appcode.replace(",", "','") + "')";
			logger("查询应用下级sql:" + sql);

			List<String> childAppcodes = runner.query(conn, sql, new ColumnListHandler<String>());
			if (childAppcodes != null && !childAppcodes.isEmpty() && childAppcodes.get(0) != null) {
				appcodes.addAll(childAppcodes);
				getChildAppcode(CollectionUtil.join(childAppcodes, ","), appcodes);
			}
		}

	}

	void getChildMenucode(String menucode, Set<String> menucodes) throws SQLException {
		if (conn == null || conn.isClosed()) {
			connect();
		}
		if (conn != null && !conn.isClosed()) {
			// 先判断是否是父模块
			String sql = "select distinct menuitemcode " + "  from sm_appmenuitem " + " where parentcode in ('"
					+ menucode.replace(",", "','") + "')";
			logger("查询应用下级sql:" + sql);
			QueryRunner runner = new QueryRunner();
			List<String> childAppcodes = runner.query(conn, sql, new ColumnListHandler<String>());
			if (childAppcodes != null && !childAppcodes.isEmpty() && childAppcodes.get(0) != null) {
				menucodes.addAll(childAppcodes);
				getChildAppcode(CollectionUtil.join(childAppcodes, ","), menucodes);
			}
		}

	}

	public void generateExportData(String appcode, boolean isNeedChildren) throws SQLException {
		int rows = tableModel.getRowCount();
		for (int i = 0; i < rows; i++) {
			tableModel.removeRow(0);
		}
		if (StrUtil.isNotBlank(appcode)) {
			String where = getAppcodes(appcode, isNeedChildren);
			for (int i = 0; i < tables.length; i++) {
				if ("SM_APPMENUITEM".equals(tables[i])) {
					String menutString = getMenuitemcodeText().getText();
					if (!StrUtil.isEmpty(menutString)) {
						Vector<String> data = new Vector<String>();
						data.add(String.valueOf((i + 1)));
						data.add(tables[i]);
						data.add(displays[i]);
						data.add(wheres[i].replace("?", "('" + menutString.replace(",", "','") + "')"));
						tableModel.addRow(data);
					}

				} else if ("DAP_DAPSYSTEM".equals(tables[i])) {
					if (!moduleIds.isEmpty()) {
						Vector<String> data = new Vector<String>();
						data.add(String.valueOf((i + 1)));
						data.add(tables[i]);
						data.add(displays[i]);
						data.add(wheres[i].replace("?",
								"('" + CollectionUtil.join(moduleIds, ",").replace(",", "','") + "')"));
						tableModel.addRow(data);
					}
				} else {
					if (!StrUtil.isEmpty(where)) {
						Vector<String> data = new Vector<String>();
						data.add(String.valueOf((i + 1)));
						data.add(tables[i]);
						data.add(displays[i]);
						data.add(wheres[i].replace("?", where));
						tableModel.addRow(data);
					}
				}

			}
		}
	}

	public void getMenucodes(String menucode, boolean isNeedChildren) throws SQLException {
		if (!StrUtil.isEmpty(menucode)) {
			Set<String> allAppcode = new TreeSet<String>();
			allAppcode.addAll(Arrays.asList(menucode.split(",")));
			if (isNeedChildren) {
				getChildMenucode(menucode, allAppcode);
			}
			Set<String> allAppcodes = new TreeSet<>();
			// 处理联查和审批页面
			allAppcode.forEach(v -> {
				if (!v.endsWith("L") && !v.endsWith("P") && !v.endsWith("A")) {
					if (!v.contains(v + "L")) {
						allAppcodes.add(v + "L");
					}
					if (!v.contains(v + "P")) {
						allAppcodes.add(v + "P");
					}
				}
				allAppcodes.add(v);
			});
			// 菜单不分模块
			Set<String> reultMenuitemcodes = checkMenuitemcode(allAppcodes);
			getMenuitemcodeText().setText(CollectionUtil.join(reultMenuitemcodes, ","));
			
			// 重新插入条件
			int rowCount = tableModel.getRowCount();
			boolean exitMenu = false;
			for (int i = 0;i<rowCount;i++) {
				if ("SM_APPMENUITEM".equals(tableModel.getValueAt(i, 1))) {
					exitMenu = true;
					tableModel.setValueAt(" MENUITEMCODE IN ('"+getMenuitemcodeText().getText().replace(",", "','")+"') ", i, 3);
				}
			}
			if (!exitMenu) {
				tableModel.addRow(new Object[] {rowCount+1,"SM_APPMENUITEM","菜单注册"," MENUITEMCODE IN ('"+getMenuitemcodeText().getText().replace(",", "','")+"') "});
			}
		}
	}

	public String getAppcodes(String appcode, boolean isNeedChildren) throws SQLException {
		moduleIds.clear();
		if (!StrUtil.isEmpty(appcode)) {
			Set<String> allAppcode = new TreeSet<String>();
			allAppcode.addAll(Arrays.asList(appcode.split(",")));
			if (isNeedChildren) {
				getChildAppcode(appcode, allAppcode);
			}
			Set<String> allAppcodes = new TreeSet<>();
			// 处理联查和审批页面
			allAppcode.forEach(v -> {
				if (!v.endsWith("L") && !v.endsWith("P") && !v.endsWith("A")) {
					if (!v.contains(v + "L")) {
						allAppcodes.add(v + "L");
					}
					if (!v.contains(v + "P")) {
						allAppcodes.add(v + "P");
					}
				}
				allAppcodes.add(v);
			});
			// 菜单不分模块
			Set<String> allMenuItemCodes = new TreeSet<>(moduleIds);
			allMenuItemCodes.addAll(allAppcodes);
			Set<String> reultMenuitemcodes = checkMenuitemcode(allMenuItemCodes);
			getMenuitemcodeText().setText(CollectionUtil.join(reultMenuitemcodes, ","));
			Set<String> reultAppcodes = checkAppcode(allAppcodes);
			if (reultAppcodes != null && !reultAppcodes.isEmpty()) {
				return "('" + CollectionUtil.join(reultAppcodes, ",").replace(",", "','") + "')";
			}

		}
		return null;
	}

	/**
	 * 判断应用编码是否存在
	 */
	public Set<String> checkAppcode(Set<String> allAppcodes) {
		try {
			String sql = "select distinct code from sm_appregister where code in " + "('"
					+ CollectionUtil.join(allAppcodes, ",").replace(",", "','") + "')";
			QueryRunner runner = new QueryRunner();
			List<String> childAppcodes = runner.query(conn, sql, new ColumnListHandler<String>());
			return new TreeSet<>(childAppcodes);
		} catch (Exception e) {
			logger("应用去重失败：" + e);
		}
		return null;
	}

	/**
	 * 判断应用编码是否存在
	 */
	public Set<String> checkMenuitemcode(Set<String> allMenuitemcodes) {
		try {
			String sql = "select distinct menuitemcode from sm_appmenuitem where menuitemcode in " + "('"
					+ CollectionUtil.join(allMenuitemcodes, ",").replace(",", "','") + "')";
			QueryRunner runner = new QueryRunner();
			List<String> childAppcodes = runner.query(conn, sql, new ColumnListHandler<String>());
			return new TreeSet<>(childAppcodes);
		} catch (Exception e) {
			logger("菜单去重失败：" + e);
		}
		return null;
	}

	/**
	 * 默认导出表名
	 */
	private String[] tables = { "BD_REFINFO", "EXCEL_TRANSLATOR", "EXCEL_OUTPUTPROCESS", "EXCEL_BILLPROCESS",
			"PUB_VOSPLITITEM", "PUB_VOCHANGE_S", "PUB_VOCHANGE_B", "PUB_VOCHANGE", "PUB_BCR_ELEM", "PUB_BCR_RULEBASE",
			"PUB_BCR_NBCR", "BD_RELATEDAPP", "PUB_BUSICLASS", "PUB_BILLACTION", "BD_FWDBILLTYPE", "BD_BILLTYPE",
			"BD_USERDEFRULE", "BD_USERDEFITEM", "BD_USERDEFRULEREF", "DAP_DAPSYSTEM", "PUB_PRINT_CELL",
			"PUB_PRINT_TEMPLATE", "PUB_SYSTEMPLATE_BASE", "PUB_FORM_PROPERTY", "PUB_QUERY_PROPERTY", "PUB_AREA",
			"PUB_PAGE_TEMPLET", "SM_APPMENUITEM", "SM_APPBUTNREGISTER", "SM_APPPAGE", "SM_APPREGISTER", "PUB_TEMPLATE_ASSIGNMENT"};
	/**
	 * 描述
	 */
	private String[] displays = { "参照", "导入导出翻译器", "导出监听", "导入监听", "单据转换规则分单规则", "单据转换规则分单对照",  "单据转换规则子表", "单据转换规则",
			"编码规则项", "默认编码规则", "编码规则", "BD_RELATEDAPP", "PUB_BUSICLASS", "单据动作", "上下游关系", "单据类型", "用户定义属性组", "用户定义属性项",
			"用户定义属性组关联项", "模块", "输出模板项", "输出模板", "默认模板", "表单字段", "查询字段", "页面区域", "页面模板", "菜单注册", "按钮", "页面", "应用注册","模板分配表"

	};
	/**
	 * 默认导出sql
	 */
	private String[] wheres = { " CODE IN  ?", " TRACLASSNAME IN ?", " BILLTYPE IN ?", " BILLTYPE IN ?",

			" PK_VOSPLITITEM IN (SELECT PK_VOSPLITITEM FROM PUB_VOCHANGE_S WHERE PK_VOCHANGE IN (SELECT PK_VOCHANGE FROM PUB_VOCHANGE WHERE DEST_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?) OR SRC_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?)))",
			" PK_VOCHANGE IN (SELECT PK_VOCHANGE FROM PUB_VOCHANGE WHERE DEST_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?) OR SRC_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?))",
			" PK_VOCHANGE IN (SELECT PK_VOCHANGE FROM PUB_VOCHANGE WHERE DEST_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?) OR SRC_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?))",
			" DEST_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?) OR SRC_BILLTYPE IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?)",

			" PK_BILLCODEBASE IN (SELECT PK_BILLCODEBASE  FROM PUB_BCR_RULEBASE WHERE NBCRCODE IN (SELECT CODE  FROM PUB_BCR_NBCR WHERE METAID IN (SELECT MDID FROM SM_APPREGISTER  WHERE CODE IN ?)))",
			" NBCRCODE IN (SELECT CODE  FROM PUB_BCR_NBCR WHERE METAID IN (SELECT MDID FROM SM_APPREGISTER  WHERE CODE IN ?))",
			" METAID IN (SELECT MDID FROM SM_APPREGISTER  WHERE CODE IN ?)", " APPCODE IN ?",
			" PK_BILLTYPEID IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?)",
			" PK_BILLTYPEID IN (SELECT PK_BILLTYPEID FROM BD_BILLTYPE WHERE NODECODE IN ?)",
			" PK_BILLTYPE IN (SELECT PK_BILLTYPECODE FROM BD_BILLTYPE WHERE NODECODE IN ?) OR PK_BACKBILLTYPE IN (SELECT PK_BILLTYPECODE FROM BD_BILLTYPE WHERE NODECODE IN ?)",
			" NODECODE IN ?",

			" PK_USERDEFRULE IN (SELECT PK_USERDEFRULE FROM BD_USERDEFRULEREF WHERE REFCLASS IN (SELECT ID FROM MD_CLASS WHERE COMPONENTID IN (SELECT COMPONENTID FROM MD_CLASS WHERE ID IN (SELECT DISTINCT MDID FROM SM_APPREGISTER WHERE  CODE IN ? AND NVL(MDID,'~') <> '~'))))",
			" PK_USERDEFRULE IN (SELECT PK_USERDEFRULE FROM BD_USERDEFRULEREF WHERE REFCLASS IN (SELECT ID FROM MD_CLASS WHERE COMPONENTID IN (SELECT COMPONENTID FROM MD_CLASS WHERE ID IN (SELECT DISTINCT MDID FROM SM_APPREGISTER WHERE  CODE IN ? AND NVL(MDID,'~') <> '~'))))",
			" REFCLASS IN (SELECT ID FROM MD_CLASS WHERE COMPONENTID IN (SELECT COMPONENTID FROM MD_CLASS WHERE ID IN (SELECT DISTINCT MDID FROM SM_APPREGISTER WHERE  CODE IN ? AND NVL(MDID,'~') <> '~')))",
			" MODULEID IN ?", " CTEMPLATEID IN (SELECT CTEMPLATEID FROM PUB_PRINT_TEMPLATE WHERE APPCODE IN ?)",
			" APPCODE IN ?", " APPCODE IN ?",
			" AREAID IN (SELECT PK_AREA FROM PUB_AREA WHERE TEMPLETID IN (SELECT PK_PAGE_TEMPLET FROM PUB_PAGE_TEMPLET WHERE APPCODE IN ?))",
			" AREAID IN (SELECT PK_AREA FROM PUB_AREA WHERE TEMPLETID IN (SELECT PK_PAGE_TEMPLET FROM PUB_PAGE_TEMPLET WHERE APPCODE IN ?))",
			" TEMPLETID IN (SELECT PK_PAGE_TEMPLET FROM PUB_PAGE_TEMPLET WHERE APPCODE IN ?)", " APPCODE IN ?",
			" MENUITEMCODE IN ? ", " APPID IN (SELECT PK_APPREGISTER FROM SM_APPREGISTER  WHERE CODE IN ?)",
			" PARENT_ID IN (SELECT PK_APPREGISTER FROM SM_APPREGISTER  WHERE CODE IN ?)", " CODE IN ?", " app_code in ? "};

}
